package office.poi.excel.utils;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import office.utils.ObjectUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
/**
 * 
 * @author Cell工具类
 *
 */
public class CellUtils {
   /**
     * 根据表格的内容得到表格
     */
	public static List<HSSFCell> findCell(HSSFSheet sheet,String s) {
		List<HSSFCell> list = new ArrayList<HSSFCell>();
		Iterator<Row> itRows = sheet.rowIterator();
		String val = "";
		while (itRows.hasNext()) {
			HSSFRow row = (HSSFRow) itRows.next();
			Iterator<Cell> itCell = row.cellIterator();
			while (itCell.hasNext()) {
				HSSFCell cell = (HSSFCell) itCell.next();
				Object obj = getCellValue(cell);
				val = ObjectUtil.isEmpty(obj)?"":obj.toString();
				if (ObjectUtil.isNotEmpty(val) && val.trim().equals(s)) {
					list.add(cell);
				}
			}
		}
		return list;
	}
	/**
	 * 通过行号 列名获取cell
	 * @param sheet
	 * @param colunmName
	 * @param rowIndex
	 * @return
	 */
	 public static List<HSSFCell> findCell(final HSSFSheet sheet,final String colunmName,final int rowIndex){ 
			List<HSSFCell> list = new ArrayList<HSSFCell>();
			Iterator<Cell> it = sheet.getRow(rowIndex).cellIterator();
			while(it.hasNext())
			{
				HSSFCell cell =  (HSSFCell) it.next();
				Object obj =  getCellValue(cell);
			    if(colunmName.equals(obj))
			    {
			    	list.add(cell);
			    }
			}
		return list;
	 }
	/**
	 * 根据表格的内容找到第一个与s相同的表格
	 * @param sheet
	 * @param s
	 * @return
	 */
	public static HSSFCell getFirstCell(HSSFSheet sheet,String s) {
		Iterator<Row> itRows = sheet.rowIterator();
		while (itRows.hasNext()) {
			HSSFRow row = (HSSFRow) itRows.next();
			Iterator<Cell> itCell = row.cellIterator();
			while (itCell.hasNext()) {
				HSSFCell cell = (HSSFCell) itCell.next();
				String val = cell.getStringCellValue();
				if (ObjectUtil.isNotEmpty(val) && val.trim().equals(s)) {
					return cell;
				}
			}
		}
		return null;
	}
	 public static HSSFCell getFirstCell(final HSSFSheet sheet,final String columnName,final int rowIndex){ 
			Iterator<Cell> it = sheet.getRow(rowIndex).cellIterator();
			while(it.hasNext())
			{
				HSSFCell cell =  (HSSFCell) it.next();
				Object obj =  getCellValue(cell);
			    if(columnName.equals(obj))
			    {
			    	return cell;
			    }
			}
		return null;
	 }
	/**
	 * 根据行列索引获取表格
	 * @param sheet
	 * @param rowIndex
	 * @param cellnum
	 * @return
	 */
	public static HSSFCell getCell(HSSFSheet sheet,int rowIndex,int cellnum)
	{
		return sheet.getRow(rowIndex).getCell(cellnum); 
	}
	
	/**
	 * 得到表格中的内容
	 * 
	 * @param cell
	 * @return
	 */
	@SuppressWarnings("static-access")
	public static Object getCellValue(HSSFCell cell) {
		
		int cellType = cell.getCellType();
		//int or integer 0  double 0
		if (cellType == cell.CELL_TYPE_NUMERIC) {
			if(HSSFDateUtil.isCellDateFormatted(cell))
			{
				return new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
			}else{
				cell.setCellType(1);
				return cell.getStringCellValue();
			}
		}else{
			cell.setCellType(1);
			return cell.getStringCellValue();
		} /*else if (cellType == cell.CELL_TYPE_STRING) {
			//date char 
			return cell.getStringCellValue();
		}else if(cellType==cell.CELL_TYPE_BOOLEAN){
			return cell.getBooleanCellValue();
		}*/
		//return null;
	}
	
}
